# 访问sqlite数据库
import sqlite3
from typing import List, Optional
import logging
from config import app_config


class ChatMessageRepository:
    """
    聊天消息表(chat_messages)的数据访问层
    表结构:
    - id: integer primary key autoincrement
    - session_id: integer not null (关联的会话ID)
    - messages_status: varchar(50) not null (消息状态)
    - created_at: DATETIME DEFAULT (datetime('now', 'localtime'))
    - updated_at: DATETIME DEFAULT (datetime('now', 'localtime'))
    """

    def __init__(self):
        """初始化数据库连接"""
        self.db_path = app_config.sqlite_db_path

    def _get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 使结果可以通过列名访问
        return conn

    def create_message(self, session_id: int, messages_status: str = "start") -> int:
        """
        创建新的聊天消息记录
        
        Args:
            session_id: 会话ID
            messages_status: 消息状态
            
        Returns:
            int: 新创建记录的ID
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO chat_messages (session_id, messages_status)
                VALUES (?, ?)
            """, (session_id, messages_status))
            conn.commit()
            return cursor.lastrowid or 0
        finally:
            conn.close()

    

    def get_message_by_id(self, message_id: int) -> Optional[dict]:
        """
        根据ID获取聊天消息记录
        
        Args:
            message_id: 消息ID
            
        Returns:
            dict: 消息记录或None（如果未找到）
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, session_id, messages_status, created_at, updated_at
                FROM chat_messages
                WHERE id = ?
            """, (message_id,))
            row = cursor.fetchone()
            return dict(row) if row else None
        except sqlite3.Error as e:
            logging.error(f"根据ID获取聊天消息时出错: {e}")
            return None
        finally:
            conn.close()

    def get_messages_by_session_id(self, session_id: int) -> List[dict]:
        """
        根据会话ID获取所有聊天消息记录
        
        Args:
            session_id: 会话ID
            
        Returns:
            List[dict]: 消息记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, session_id, messages_status, created_at, updated_at
                FROM chat_messages
                WHERE session_id = ?
                ORDER BY created_at ASC
            """, (session_id,))
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
        except sqlite3.Error as e:
            logging.error(f"根据会话ID获取聊天消息时出错: {e}")
            return []
        finally:
            conn.close()

    def get_all_messages(self) -> List[dict]:
        """
        获取所有聊天消息记录
        
        Returns:
            List[dict]: 所有消息记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, session_id, messages_status, created_at, updated_at
                FROM chat_messages
                ORDER BY created_at ASC
            """)
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
        except sqlite3.Error as e:
            logging.error(f"获取所有聊天消息时出错: {e}")
            return []
        finally:
            conn.close()

    def update_message_status(self, message_id: int, messages_status: str) -> bool:
        """
        更新聊天消息状态
        
        Args:
            message_id: 消息ID
            messages_status: 新的消息状态
            
        Returns:
            bool: 更新是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE chat_messages
                SET messages_status = ?, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (messages_status, message_id))
            conn.commit()
            return cursor.rowcount > 0
        finally:
            conn.close()

    def log_ASR_PENDING(self, message_id: int) -> bool:
        """
        将消息状态更新为 ASR_PENDING
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "ASR_PENDING")
    
    def log_ASR_DONE(self, message_id: int) -> bool:
        """
        将消息状态更新为 ASR_DONE
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "ASR_DONE")
    
    def log_ASR_FAILED(self, message_id: int) -> bool:
        """
        将消息状态更新为 ASR_FAILED
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "ASR_FAILED")
    
    def log_LLM_PENDING(self, message_id: int) -> bool:
        """
        将消息状态更新为 LLM_PENDING
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "LLM_PENDING")
    
    def log_LLM_DONE(self, message_id: int) -> bool:
        """
        将消息状态更新为 LLM_DONE
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "LLM_DONE")
    
    def log_LLM_FAILED(self, message_id: int) -> bool:
        """
        将消息状态更新为 LLM_FAILED
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "LLM_FAILED")

    def log_TTS_PENDING(self, message_id: int) -> bool:
        """
        将消息状态更新为 TTS_PENDING
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "TTS_PENDING")
    
    def log_TTS_DONE(self, message_id: int) -> bool:
        """
        将消息状态更新为 TTS_DONE
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "TTS_DONE")
    
    def log_TTS_FAILED(self, message_id: int) -> bool:
        """
        将消息状态更新为 TTS_FAILED
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "TTS_FAILED")
    
    def log_END(self, message_id: int) -> bool:
        """
        将消息状态更新为 END
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 更新是否成功
        """
        return self.update_message_status(message_id, "END")

    def delete_message(self, message_id: int) -> bool:
        """
        删除聊天消息记录
        
        Args:
            message_id: 消息ID
            
        Returns:
            bool: 删除是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM chat_messages WHERE id = ?", (message_id,))
            conn.commit()
            return cursor.rowcount > 0
        finally:
            conn.close()

    def delete_messages_by_session_id(self, session_id: int) -> int:
        """
        根据会话ID删除所有聊天消息记录
        
        Args:
            session_id: 会话ID
            
        Returns:
            int: 删除的记录数量
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM chat_messages WHERE session_id = ?", (session_id,))
            conn.commit()
            return cursor.rowcount
        finally:
            conn.close()


# 创建全局实例
chat_message_repo = ChatMessageRepository()
